/*
 * Copyright (C) 2015 Miquel Sas
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package traderlib.core.data.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import traderlib.core.data.entity.FieldList;
import traderlib.core.data.types.Types;

/**
 * Utility class to access the JDBC metadata.
 *
 * @author Miquel Sas
 */
public class MetaData {

	/**
	 * Helper to create columns.
	 *
	 * @param name The column name.
	 * @param description The description.
	 * @param type The column type.
	 * @param length The length.
	 * @param decs The number of decimals.
	 * @return the Column.
	 */
	private static Column createColumn(String name, String description, Types type, int length, int decimals, boolean primaryKey) {
		Column column = new Column();
		column.setName(name);
		column.setDescription(description);
		column.setType(type);
		column.setLength(length);
		column.setDecimals(decimals);
		column.setPrimaryKey(primaryKey);
		return column;
	}

	/**
	 * Helper to create records.
	 *
	 * @param columns The array of columns
	 * @param pointers The array of primary key pointers
	 * @return the Record.
	 */
	private static Record createRecord(Column[] columns) {
		FieldList fieldList = new FieldList();
		for (Column column : columns) {
			fieldList.addField(column);
		}
		Record record = new Record();
		record.setColumnList(fieldList);
		record.setValues(fieldList.getDefaultValues());
		return record;
	}

	/**
	 * Returns the description name for the metadata index types.
	 *
	 * @return The description name.
	 * @param type The index type.
	 */
	public static String getDescriptionIndexType(int type) {
		switch (type) {
			case DatabaseMetaData.tableIndexStatistic:
				return "STATISTIC";
			case DatabaseMetaData.tableIndexClustered:
				return "CLUSTERED";
			case DatabaseMetaData.tableIndexHashed:
				return "HASHED";
			case DatabaseMetaData.tableIndexOther:
				return "OTHER";
		}
		return "";
	}

	/**
	 * Returns the description name of the metadata nullable type.
	 *
	 * @return The description name.
	 * @param nullable The nullable type.
	 */
	public static String getDescriptionTypeNullable(int nullable) {
		switch (nullable) {
			case DatabaseMetaData.typeNoNulls:
				return "NO";
			case DatabaseMetaData.typeNullable:
				return "YES";
			case DatabaseMetaData.typeNullableUnknown:
				return "UNKNOWN";
		}
		return "";
	}

	/**
	 * Catalog info class.
	 */
	public static class CatalogInfo {

		public final static int TABLE_CAT = 0;

		/**
		 * Return the default record.
		 *
		 * @return The Record.
		 */
		public static Record getRecord() {
			Column[] columns = new Column[]{
				createColumn("TABLE_CAT", "Catalog", Types.String, 40, 0, true)
			};
			return createRecord(columns);
		}
	}

	/**
	 * Column info class.
	 */
	public static class ColumnInfo {

		public final static int TABLE_CAT = 0;
		public final static int TABLE_SCHEM = 1;
		public final static int TABLE_NAME = 2;
		public final static int COLUMN_NAME = 3;
		public final static int DATA_TYPE = 4;
		public final static int TYPE_NAME = 5;
		public final static int COLUMN_SIZE = 6;
		public final static int BUFFER_LENGTH = 7;
		public final static int DECIMAL_DIGITS = 8;
		public final static int NUM_PREC_RADIX = 9;
		public final static int NULLABLE = 10;
		public final static int REMARKS = 11;
		public final static int COLUMN_DEF = 12;
		public final static int SQL_DATA_TYPE = 13;
		public final static int SQL_DATETIME_SUB = 14;
		public final static int CHAR_OCTET_LENGTH = 15;
		public final static int ORDINAL_POSITION = 16;
		public final static int IS_NULLABLE = 17;
		public final static int DATA_TYPE_NAME = 18;

		/**
		 * Return the default record.
		 *
		 * @return the Record
		 */
		public static Record getRecord() {
			Column[] columns = new Column[]{
				createColumn("TABLE_CAT", "Catalog", Types.String, 40, 0, true),
				createColumn("TABLE_SCHEM", "Schema", Types.String, 40, 0, true),
				createColumn("TABLE_NAME", "Table", Types.String, 40, 0, false),
				createColumn("COLUMN_NAME", "Column", Types.String, 40, 0, false),
				createColumn("DATA_TYPE", "Data type", Types.Integer, 0, 0, false),
				createColumn("TYPE_NAME", "Type name", Types.String, 20, 0, false),
				createColumn("COLUMN_SIZE", "Column size", Types.Long, 0, 0, false),
				createColumn("BUFFER_LENGTH", "Buffer length", Types.Integer, 0, 0, false),
				createColumn("DECIMAL_DIGITS", "Decimal digits", Types.Integer, 0, 0, false),
				createColumn("NUM_PREC_RADIX", "Num prec radix", Types.Integer, 0, 0, false),
				createColumn("NULLABLE", "Nullable", Types.Integer, 0, 0, false),
				createColumn("REMARKS", "Remarks", Types.String, 128, 0, false),
				createColumn("COLUMN_DEF", "Column def", Types.String, 128, 0, false),
				createColumn("SQL_DATA_TYPE", "SQL data type", Types.Integer, 0, 0, false),
				createColumn("SQL_DATETIME_SUB", "SQL date time sub", Types.Integer, 0, 0, false),
				createColumn("CHAR_OCTET_LENGTH", "Char octet length", Types.Long, 0, 0, false),
				createColumn("ORDINAL_POSITION", "Ordinal position", Types.Integer, 0, 0, true),
				createColumn("IS_NULLABLE", "Is nullable", Types.String, 5, 0, false),
				createColumn("DATA_TYPE_NAME", "Data type name", Types.String, 20, 0, false)
			};
			return createRecord(columns);
		}
	}

	/**
	 * Index info class.
	 */
	public static class IndexInfo {

		public final static int TABLE_CAT = 0;
		public final static int TABLE_SCHEM = 1;
		public final static int TABLE_NAME = 2;
		public final static int NON_UNIQUE = 3;
		public final static int INDEX_QUALIFIER = 4;
		public final static int INDEX_NAME = 5;
		public final static int TYPE = 6;
		public final static int ORDINAL_POSITION = 7;
		public final static int COLUMN_NAME = 8;
		public final static int ASC_OR_DESC = 9;
		public final static int CARDINALITY = 10;
		public final static int PAGES = 11;
		public final static int FILTER_CONDITION = 12;
		public final static int INDEX_TYPE_DESC = 13;

		/**
		 * Return the default record.
		 *
		 * @return the Record
		 */
		public static Record getRecord() {
			Column[] columns = new Column[]{
				createColumn("TABLE_CAT", "Catalog", Types.String, 40, 0, true),
				createColumn("TABLE_SCHEM", "Schema", Types.String, 40, 0, true),
				createColumn("TABLE_NAME", "Table", Types.String, 40, 0, true),
				createColumn("NON_UNIQUE", "Non unique", Types.Boolean, 1, 0, false),
				createColumn("INDEX_QUALIFIER", "Index qualifier", Types.String, 40, 0, false),
				createColumn("INDEX_NAME", "Index name", Types.String, 40, 0, false),
				createColumn("TYPE", "Index type", Types.Integer, 40, 0, false),
				createColumn("ORDINAL_POSITION", "Ordinal position", Types.Integer, 0, 0, false),
				createColumn("COLUMN_NAME", "Column name", Types.String, 0, 0, false),
				createColumn("ASC_OR_DESC", "Asc/desc", Types.String, 2, 0, false),
				createColumn("CARDINALITY", "Cardinality", Types.Integer, 0, 0, false),
				createColumn("PAGES", "Pages", Types.Integer, 0, 0, false),
				createColumn("FILTER_CONDITION", "Filter condition", Types.String, 128, 0, false),
				createColumn("INDEX_TYPE_DESC", "Index type desc", Types.String, 20, 0, false)
			};
			return createRecord(columns);
		}
	}

	/**
	 * Schema info class.
	 */
	public static class SchemaInfo {

		public final static int TABLE_SCHEM = 0;

		/**
		 * Return the default record.
		 *
		 * @return the Record
		 */
		public static Record getRecord() {
			Column[] columns = new Column[]{
				createColumn("TABLE_SCHEM", "Schema", Types.String, 40, 0, true)
			};
			return createRecord(columns);
		}
	}

	/**
	 * Table info class.
	 */
	public static class TableInfo {

		public final static int TABLE_CAT = 0;
		public final static int TABLE_SCHEM = 1;
		public final static int TABLE_NAME = 2;
		public final static int TABLE_TYPE = 3;
		public final static int REMARKS = 4;

		/**
		 * Return the default record.
		 *
		 * @return the Record
		 */
		public static Record getRecord() {
			Column[] columns = new Column[]{
				createColumn("TABLE_CAT", "Catalog", Types.String, 40, 0, true),
				createColumn("TABLE_SCHEM", "Schema", Types.String, 40, 0, true),
				createColumn("TABLE_NAME", "Table", Types.String, 40, 0, true),
				createColumn("TABLE_TYPE", "Table type", Types.String, 40, 0, false),
				createColumn("REMARKS", "Remarks", Types.String, 128, 0, false)
			};
			return createRecord(columns);
		}

	}

	/**
	 * Primary key info.
	 */
	public static class PrimaryKeyInfo {

		public final static int TABLE_CAT = 0;
		public final static int TABLE_SCHEM = 1;
		public final static int TABLE_NAME = 2;
		public final static int COLUMN_NAME = 3;
		public final static int KEY_SEQ = 4;
		public final static int PK_NAME = 5;

		/**
		 * Return the default record.
		 *
		 * @return the Record
		 */
		public static Record getRecord() {
			Column[] columns = new Column[]{
				createColumn("TABLE_CAT", "Catalog", Types.String, 40, 0, true),
				createColumn("TABLE_SCHEM", "Schema", Types.String, 40, 0, true),
				createColumn("TABLE_NAME", "Table", Types.String, 40, 0, true),
				createColumn("COLUMN_NAME", "Column name", Types.String, 40, 0, true),
				createColumn("KEY_SEQ", "Key seq", Types.Integer, 4, 0, true),
				createColumn("PK_NAME", "PK name", Types.String, 40, 0, false)
			};
			return createRecord(columns);
		}
	}

	/**
	 * Type info class.
	 */
	public static class TypeInfo {

		public final static int TYPE_NAME = 0;
		public final static int DATA_TYPE = 1;
		public final static int PRECISION = 2;
		public final static int LITERAL_PREFIX = 3;
		public final static int LITERAL_SUFFIX = 4;
		public final static int CREATE_PARAMS = 5;
		public final static int NULLABLE = 6;
		public final static int CASE_SENSITIVE = 7;
		public final static int SEARCHABLE = 8;
		public final static int UNSIGNED_ATTRIBUTE = 9;
		public final static int FIXED_PREC_SCALE = 10;
		public final static int AUTO_INCREMENT = 11;
		public final static int LOCAL_TYPE_NAME = 12;
		public final static int MINIMUM_SCALE = 13;
		public final static int MAXIMUM_SCALE = 14;
		public final static int SQL_DATA_TYPE = 15;
		public final static int SQL_DATETIME_SUB = 16;
		public final static int NUM_PREC_RADIX = 17;
		public final static int DATA_TYPE_NAME = 18;
		public final static int NULLABLE_DESC = 19;

		/**
		 * Return the default record.
		 *
		 * @return the Record
		 */
		public static Record getRecord() {
			Column[] columns = new Column[]{
				createColumn("TYPE_NAME", "Type name", Types.String, 20, 0, true),
				createColumn("DATA_TYPE", "Data type", Types.Integer, 0, 0, false),
				createColumn("PRECISION", "Precision", Types.Long, 0, 0, false),
				createColumn("LITERAL_PREFIX", "Literal prefix", Types.String, 5, 0, false),
				createColumn("LITERAL_SUFFIX", "Literal suffix", Types.String, 5, 0, false),
				createColumn("CREATE_PARAMS", "Create params", Types.String, 20, 0, false),
				createColumn("NULLABLE", "Nullable", Types.Integer, 0, 0, false),
				createColumn("CASE_SENSITIVE", "Case sensitive", Types.Boolean, 0, 0, false),
				createColumn("SEARCHABLE", "Searcheable", Types.Integer, 0, 0, false),
				createColumn("UNSIGNED_ATTRIBUTE", "Unsigned attribute", Types.Boolean, 0, 0, false),
				createColumn("FIXED_PREC_SCALE", "Fixed prec scale", Types.Boolean, 0, 0, false),
				createColumn("AUTO_INCREMENT", "Auto increment", Types.Boolean, 0, 0, false),
				createColumn("LOCAL_TYPE_NAME", "Local type name", Types.String, 20, 0, false),
				createColumn("MINIMUM_SCALE", "Minimum scale", Types.Integer, 0, 0, false),
				createColumn("MAXIMUM_SCALE", "Maximum scale", Types.Integer, 0, 0, false),
				createColumn("SQL_DATA_TYPE", "SQL data type", Types.Integer, 0, 0, false),
				createColumn("SQL_DATETIME_SUB", "SQL date time sub", Types.Integer, 0, 0, false),
				createColumn("NUM_PREC_RADIX", "Num prec radix", Types.Integer, 0, 0, false),
				createColumn("DATA_TYPE_NAME", "Data type name", Types.String, 20, 0, false),
				createColumn("NULLABLE_DESC", "Nullable desc", Types.String, 20, 0, false)
			};
			return createRecord(columns);
		}
	}

	/**
	 * Foreign keys info.
	 */
	public static class ForeignKeyInfo {

		public final static int PKTABLE_CAT = 0;
		public final static int PKTABLE_SCHEM = 1;
		public final static int PKTABLE_NAME = 2;
		public final static int PKCOLUMN_NAME = 3;
		public final static int FKTABLE_CAT = 4;
		public final static int FKTABLE_SCHEM = 5;
		public final static int FKTABLE_NAME = 6;
		public final static int FKCOLUMN_NAME = 7;
		public final static int KEY_SEQ = 8;
		public final static int UPDATE_RULE = 9;
		public final static int DELETE_RULE = 10;
		public final static int FK_NAME = 11;
		public final static int PK_NAME = 12;
		public final static int DEFERRABILITY = 13;

		/**
		 * Return the default record.
		 *
		 * @return the Record
		 */
		public static Record getRecord() {
			Column[] columns = new Column[]{
				createColumn("PKTABLE_CAT", "PK Table catalog", Types.String, 20, 0, true),
				createColumn("PKTABLE_SCHEM", "PK Table schema", Types.String, 20, 0, false),
				createColumn("PKTABLE_NAME", "PK Table name", Types.String, 20, 0, false),
				createColumn("PKCOLUMN_NAME", "PK Column name", Types.String, 20, 0, false),
				createColumn("FKTABLE_CAT", "FK Table catalog", Types.String, 20, 0, false),
				createColumn("FKTABLE_SCHEM", "FK Table schema", Types.String, 20, 0, false),
				createColumn("FKTABLE_NAME", "FK Table name", Types.String, 20, 0, false),
				createColumn("FKCOLUMN_NAME", "FK Column name", Types.String, 20, 0, false),
				createColumn("KEY_SEQ", "Key sequence", Types.Integer, 2, 0, false),
				createColumn("UPDATE_RULE", "Update rule", Types.Integer, 2, 0, false),
				createColumn("DELETE_RULE", "Delete rule", Types.Integer, 2, 0, false),
				createColumn("FK_NAME", "FK Name", Types.String, 20, 0, false),
				createColumn("PK_NAME", "PK Name", Types.String, 20, 0, false),
				createColumn("DEFERRABILITY", "Deferrability", Types.Integer, 20, 0, false)
			};
			return createRecord(columns);
		}
	}
	/**
	 * The underlying database engine.
	 */
	private final DBEngine dbEngine;

	/**
	 * Constructor assigning the database engine.
	 *
	 * @param dbEngine The database engine.
	 */
	public MetaData(DBEngine dbEngine) {
		this.dbEngine = dbEngine;
	}

	/**
	 * Returns the catalogs recordset.
	 *
	 * @return The recordset.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetCatalogs() throws SQLException {
		FieldList columnList = CatalogInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getCatalogs();
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Returns the schema recordset.
	 *
	 * @return The recordset.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetSchemas() throws SQLException {
		FieldList columnList = SchemaInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getSchemas();
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Returns the table recordset.
	 *
	 * @param schema The schema name or null
	 * @return A record set with the list of tables
	 * @throws SQLException
	 */
	public RecordSet getRecordSetTables(String schema) throws SQLException {
		return getRecordSetTables(null, schema, null, null);
	}

	/**
	 * Returns the table recordset.
	 *
	 * @param schema The schema name or null
	 * @param table A prefix of table names
	 * @return A record set with the list of tables
	 * @throws SQLException
	 */
	public RecordSet getRecordSetTables(String schema, String table) throws SQLException {
		return getRecordSetTables(null, schema, table, null);
	}

	/**
	 * Returns the table recordset.
	 *
	 * @param catalog The table catalalog or null
	 * @param schema The table schema or null
	 * @param table The table name prefix or null
	 * @return A recordset with table definition.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetTables(String catalog, String schema, String table) throws SQLException {
		return getRecordSetTables(catalog, schema, table, null);
	}

	/**
	 * Returns the table recordset.
	 *
	 * @param catalog The table catalalog or null
	 * @param schema The table schema or null
	 * @param table The table name prefix or null
	 * @param types An array of possible table types
	 * @return A recordset with table definition.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetTables(String catalog, String schema, String table, String[] types) throws SQLException {
		FieldList columnList = TableInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getTables(catalog, schema, table, types);
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Returns a record set with information about the columns of a table.
	 *
	 * @return The record set.
	 * @param schema The schema name.
	 * @param table The table name.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetColumns(String schema, String table) throws SQLException {
		return getRecordSetColumns(null, schema, table);
	}

	/**
	 * Returns a record set with information about the columns of a table.
	 *
	 * @return The record set.
	 * @param catalog The catalog name
	 * @param schema The schema name.
	 * @param table The table name.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetColumns(String catalog, String schema, String table) throws SQLException {
		FieldList columnList = ColumnInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getColumns(catalog, schema, table, null);
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Returns a record set with information about the indexes of a table.
	 *
	 * @return The record set.
	 * @param schema The schema name.
	 * @param table The table name.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetIndexes(String schema, String table) throws SQLException {
		return getRecordSetIndexes(null, schema, table);
	}

	/**
	 * Returns a record set with information about the indexes of a table.
	 *
	 * @return The record set.
	 * @param catalog The catalog name
	 * @param schema The schema name.
	 * @param table The table name.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetIndexes(String catalog, String schema, String table) throws SQLException {
		FieldList columnList = IndexInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getIndexInfo(catalog, schema, table, false, false);
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Returns a record set with information about the primary key of a table.
	 *
	 * @param schema
	 * @param table
	 * @return The RecordSet
	 * @throws SQLException
	 */
	public RecordSet getRecordSetPrimaryKey(String schema, String table) throws SQLException {
		return getRecordSetPrimaryKey(null, schema, table);
	}

	/**
	 * Returns a record set with information about the primary key of a table.
	 *
	 * @param catalog
	 * @param schema
	 * @param table
	 * @return the RecordSet
	 * @throws SQLException
	 */
	public RecordSet getRecordSetPrimaryKey(String catalog, String schema, String table) throws SQLException {
		FieldList columnList = IndexInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getPrimaryKeys(catalog, schema, table);
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Returns a record set with information about the imported keys of a table. In our db system theese are foreign
	 * keys of the argument table.
	 *
	 * @return The record set.
	 * @param catalog The catalog name
	 * @param schema The schema name.
	 * @param table The table name.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetImportedKeys(String catalog, String schema, String table) throws SQLException {
		FieldList columnList = IndexInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getImportedKeys(catalog, schema, table);
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Returns a record set with information about the exported keys of a table. In our db system theese are referrers
	 * of the argument table.
	 *
	 * @return The record set.
	 * @param catalog The catalog name
	 * @param schema The schema name.
	 * @param table The table name.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetExportedKeys(String catalog, String schema, String table) throws SQLException {
		FieldList columnList = IndexInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getExportedKeys(catalog, schema, table);
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Returns a record set with information about the database types.
	 *
	 * @return The record set.
	 * @throws SQLException
	 */
	public RecordSet getRecordSetTypes() throws SQLException {
		FieldList columnList = IndexInfo.getRecord().getColumnList();
		Connection cn = dbEngine.getConnection();
		ResultSet rs = cn.getMetaData().getTypeInfo();
		return readRecordSet(cn, rs, columnList);
	}

	/**
	 * Reads the record set.
	 * @param cn The connection
	 * @param rs The resultset
	 * @param columnList The list of columns.
	 * @return
	 * @throws SQLException 
	 */
	@SuppressWarnings("ConvertToTryWithResources")
	private RecordSet readRecordSet(Connection cn, ResultSet rs, FieldList columnList) throws SQLException {
		RecordSet recordSet = new RecordSet();
		recordSet.setColumnList(columnList);
		try {
			cn = dbEngine.getConnection();
			while (rs.next()) {
				Record record = DBUtils.readRecord(columnList, rs);
				recordSet.add(record);
			}
			rs.close();
		} finally {
			if (cn != null && !cn.isClosed()) {
				cn.close();
			}
		}
		return recordSet;
	}
}
